IF OBJECT_ID('UpsertServerOS','P') IS NOT NULL
   DROP PROCEDURE dbo.UpsertServerOS
GO

------------------------------------------------------------------------------------
--  PROCEDURE NAME: dbo.UpsertServerOS    
--     DESCRIPTION: Inserts or updates a record in the ServerOS table
------------------------------------------------------------------------------------
--   REFERENCED BY: Upsert-ServerOS.ps1
--    PROJECT NAME: iSQLPS
------------------------------------------------------------------------------------
--  CHANGE HISTORY:
-- DATE        MODIFIED   DESCRIPTION   
------------------------------------------------------------------------------------
-- 02.01.2010  SYoung     Initial creation
-- 05.26.2010  SYoung     Applied database standards
------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[UpsertServerOS]     
       @HostName [varchar] (128),  
       @Region [char] (2),  
       @Location [char] (2),  
       @Description [varchar] (500),  
       @PrimaryBU [varchar] (128),  
       @TimeZone [varchar] (128),  
       @EnableDaylightSavingsTime [bit],  
       @Domain [varchar] (128),  
       @Manufacturer [varchar] (128),  
       @Model [varchar] (128),  
       @SystemType [varchar] (128),  
       @SystemStartupOptions [varchar] (128),  
       @ProcessorPhysical [tinyint],  
       @ProcessorLogical [tinyint],  
       @ProcessorModel [varchar] (128),  
       @TotalPhysicalMemory [bigint],  
       @CountryCode [varchar] (128),  
       @LastBootUpTime [smalldatetime],  
       @Locale [varchar] (128),  
       @OS [varchar] (128),  
       @Version [varchar] (128),  
       @ServicePackMajorVersion [varchar] (10),  
       @ServicePackMinorVersion [varchar] (10),  
       @BuildNumber [varchar] (20),  
       @InstallDate [smalldatetime],  
       @TotalVisibleMemorySize [bigint],  
       @TotalVirtualMemorySize [bigint],  
       @PagingFileSize [bigint],  
       @IP1 [varchar] (128),  
       @IP2 [varchar] (128),                 
       @IP3 [varchar] (128),  
       @IP4 [varchar] (128),  
       @IP5 [varchar] (128),  
       @IP6 [varchar] (128),  
       @Active tinyint = NULL

AS

SET NOCOUNT ON
  
DECLARE @ERRORCODE [int], 
        @ERRMSG [varchar] (128)  
  
-- If the host doesn't exist in the Hosts table, then perform an insertion.  
IF NOT EXISTS (SELECT ServerOSID FROM dbo.ServerOS WHERE HostName = @HostName)  
   BEGIN
   INSERT [dbo].[ServerOS](  
          HostName  
        , Region  
        , Location  
        , [Description]  
        , PrimaryBU  
        , TimeZone  
        , EnableDaylightSavingsTime  
        , Domain  
        , Manufacturer  
        , Model  
        , SystemType  
        , SystemStartupOptions  
        , ProcessorPhysical  
        , ProcessorLogical  
        , ProcessorModel  
        , TotalPhysicalMemory  
        , CountryCode  
        , LastBootUpTime  
        , Locale  
        , OS  
        , [Version]  
        , ServicePackMajorVersion  
        , ServicePackMinorVersion  
        , BuildNumber  
        , InstallDate  
        , TotalVisibleMemorySize  
        , TotalVirtualMemorySize  
        , PagingFileSize  
        , IP1  
        , IP2  
        , IP3  
        , IP4  
        , IP5  
        , IP6  
        , CreateDate  
        , UpdateDate  
        , Active)  
   VALUES (@HostName  
        , @Region  
        , @Location  
        , @Description  
        , @PrimaryBU  
        , @TimeZone  
        , @EnableDaylightSavingsTime  
        , @Domain  
        , @Manufacturer  
        , @Model  
        , @SystemType  
        , @SystemStartupOptions  
        , @ProcessorPhysical  
        , @ProcessorLogical  
        , @ProcessorModel  
        , @TotalPhysicalMemory  
        , @CountryCode  
        , @LastBootUpTime  
        , @Locale  
        , @OS  
        , @Version  
        , @ServicePackMajorVersion  
        , @ServicePackMinorVersion  
        , @BuildNumber  
        , @InstallDate  
        , @TotalVisibleMemorySize  
        , @TotalVirtualMemorySize  
        , @PagingFileSize  
        , @IP1  
        , @IP2  
        , @IP3  
        , @IP4  
        , @IP5  
        , @IP6  
        , GETDATE()  
        , GETDATE()  
        , @Active)
END
-- If the host already exists in the Hosts table, then perform an update.  
ELSE  
   BEGIN
   UPDATE [dbo].[ServerOS]  
      SET [Region] = @Region  
        , [Location] = @Location  
        , [Description] = @Description  
        , [PrimaryBU] = @PrimaryBU  
        , [TimeZone] = @TimeZone  
        , [EnableDaylightSavingsTime] = @EnableDaylightSavingsTime  
        , [Domain] = @Domain  
        , [Manufacturer] = @Manufacturer  
        , [Model] = @Model  
        , [SystemType] = @SystemType  
        , [SystemStartupOptions] = @SystemStartupOptions   
        , [ProcessorPhysical] = @ProcessorPhysical  
        , [ProcessorLogical] = @ProcessorLogical  
        , [ProcessorModel] = @ProcessorModel  
        , [TotalPhysicalMemory] = @TotalPhysicalMemory  
        , [CountryCode] = @CountryCode  
        , [LastBootUpTime] = @LastBootUpTime  
        , [Locale] = @Locale  
        , [OS] = @OS  
        , [Version] = @Version  
        , [ServicePackMajorVersion] = @ServicePackMajorVersion  
        , [ServicePackMinorVersion] = @ServicePackMinorVersion  
        , [BuildNumber] = @BuildNumber  
        , [InstallDate] = @InstallDate  
        , [TotalVisibleMemorySize] = @TotalVisibleMemorySize  
        , [TotalVirtualMemorySize] = @TotalVirtualMemorySize  
        , [PagingFileSize] = @PagingFileSize  
        , [IP1] = @IP1  
        , [IP2] = @IP2   
        , [IP3] = @IP3   
        , [IP4] = @IP4   
        , [IP5] = @IP5   
        , [IP6] = @IP6   
        , [UpdateDate] = GETDATE()  
        , [Active] = @Active  
    WHERE hostName = @hostName  
END
  
SET @ERRORCODE = @@ERROR  
    IF @ERRORCODE <> 0   
       BEGIN  
       SET @ERRMSG = 'Insert failed - ' + OBJECT_NAME(@@PROCID)  
       SET @ERRMSG = @ERRMSG + ' Error Code: ' + RTRIM(CONVERT(CHAR, @ERRORCODE))   
       RAISERROR (@ERRMSG, 16, 1)  
       RETURN (-1)  
    END  
    ELSE  
      RETURN (0)
GO

EXEC sp_addextendedproperty @name = N'MS_Description',
                           @value = N'Inserts or updates a record in the ServerOS table.',
                      @level0type = N'USER',
                      @level0name = N'dbo',
                      @level1type = N'PROCEDURE',
                      @level1name = N'UpsertServerOS'
GO
